{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 第4阶段_第2讲_描述性统计分析\n",
    "\n",
    "## 学习目标\n",
    "1. 掌握集中趋势的三大指标(均值、中位数、众数)\n",
    "2. 理解离散程度的度量方法(方差、标准差、四分位数)\n",
    "3. 学会使用Pandas进行分布形态分析(偏度、峰度)\n",
    "4. 熟练运用describe()等统计函数进行快速数据概览\n",
    "5. 掌握分组统计和交叉表分析方法\n",
    "6. 能够用统计指标发现数据异常和业务问题\n",
    "\n",
    "## 为什么需要描述性统计?\n",
    "\n",
    "描述性统计是数据分析的第一步,帮助我们:\n",
    "- **快速了解数据全貌**: 通过几个关键指标了解数据的基本特征\n",
    "- **发现数据异常**: 通过统计指标识别极端值、异常分布\n",
    "- **支撑业务决策**: 用数据说话,如平均工资、销售波动等\n",
    "- **为深入分析打基础**: 是探索性分析、建模的前提\n",
    "\n",
    "## Excel vs Pandas对比\n",
    "\n",
    "| 功能 | Excel | Pandas |\n",
    "|------|-------|--------|\n",
    "| 平均值 | =AVERAGE(A1:A100) | df['column'].mean() |\n",
    "| 中位数 | =MEDIAN(A1:A100) | df['column'].median() |\n",
    "| 众数 | =MODE(A1:A100) | df['column'].mode() |\n",
    "| 标准差 | =STDEV.S(A1:A100) | df['column'].std() |\n",
    "| 方差 | =VAR.S(A1:A100) | df['column'].var() |\n",
    "| 最大最小值 | =MAX(), =MIN() | df['column'].max(), min() |\n",
    "| 四分位数 | =QUARTILE(A1:A100,1) | df['column'].quantile(0.25) |\n",
    "| 综合统计 | 数据分析-描述统计 | df.describe() |\n",
    "| 分组统计 | 数据透视表 | df.groupby().agg() |\n",
    "| 相关系数 | =CORREL(A1:A100,B1:B100) | df.corr() |"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 导入必要的库\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "from scipy import stats\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "\n",
    "# 设置中文显示\n",
    "plt.rcParams['font.sans-serif'] = ['Arial Unicode MS', 'SimHei']\n",
    "plt.rcParams['axes.unicode_minus'] = False\n",
    "\n",
    "# 设置显示选项\n",
    "pd.set_option('display.max_columns', None)\n",
    "pd.set_option('display.width', 1000)\n",
    "pd.set_option('display.float_format', '{:.2f}'.format)\n",
    "\n",
    "print(\"环境配置完成!\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 一、集中趋势度量(Measures of Central Tendency)\n",
    "\n",
    "集中趋势描述数据的\"中心位置\",回答\"数据的典型值是多少\"的问题。\n",
    "\n",
    "### 1.1 均值(Mean)\n",
    "- **定义**: 所有数据之和除以数据个数\n",
    "- **特点**: 受极端值影响大\n",
    "- **适用**: 数据分布较均匀、无明显极端值时\n",
    "- **公式**: $\\bar{x} = \\frac{\\sum_{i=1}^{n} x_i}{n}$\n",
    "\n",
    "### 1.2 中位数(Median)\n",
    "- **定义**: 将数据从小到大排序后,位于中间位置的值\n",
    "- **特点**: 不受极端值影响,更稳健\n",
    "- **适用**: 数据有极端值或偏态分布时(如收入、房价)\n",
    "\n",
    "### 1.3 众数(Mode)\n",
    "- **定义**: 出现频次最多的值\n",
    "- **特点**: 可用于分类数据\n",
    "- **适用**: 离散数据、分类数据(如最畅销商品)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 创建示例数据:某公司员工月薪(单位:千元)\n",
    "np.random.seed(42)\n",
    "\n",
    "# 生成正态分布的薪资数据\n",
    "normal_salaries = np.random.normal(loc=15, scale=3, size=95)  # 95个普通员工\n",
    "# 添加几个高管薪资(极端值)\n",
    "executive_salaries = np.array([50, 80, 120, 150, 200])  # 5个高管\n",
    "\n",
    "all_salaries = np.concatenate([normal_salaries, executive_salaries])\n",
    "salary_data = pd.DataFrame({\n",
    "    '员工编号': [f'EMP{str(i).zfill(3)}' for i in range(1, 101)],\n",
    "    '月薪': all_salaries\n",
    "})\n",
    "\n",
    "print(\"员工薪资数据预览:\")\n",
    "print(salary_data.head(10))\n",
    "print(f\"\\n数据集大小: {len(salary_data)} 条记录\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 计算集中趋势的三大指标\n",
    "mean_salary = salary_data['月薪'].mean()\n",
    "median_salary = salary_data['月薪'].median()\n",
    "mode_salary = salary_data['月薪'].mode()[0]  # mode()返回Series,取第一个\n",
    "\n",
    "print(\"=\"*60)\n",
    "print(\"集中趋势分析结果\")\n",
    "print(\"=\"*60)\n",
    "print(f\"平均工资(Mean):    {mean_salary:.2f}千元\")\n",
    "print(f\"中位数工资(Median): {median_salary:.2f}千元\")\n",
    "print(f\"众数工资(Mode):    {mode_salary:.2f}千元\")\n",
    "print(\"=\"*60)\n",
    "\n",
    "print(\"\\n💡 分析结论:\")\n",
    "print(f\"平均工资({mean_salary:.2f})明显高于中位数({median_salary:.2f}),\")\n",
    "print(\"说明存在高薪员工拉高了平均值,工资分布呈现右偏态。\")\n",
    "print(\"中位数更能反映大多数员工的薪资水平。\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 可视化集中趋势\n",
    "fig, axes = plt.subplots(1, 2, figsize=(14, 5))\n",
    "\n",
    "# 左图:直方图+密度曲线\n",
    "axes[0].hist(salary_data['月薪'], bins=30, alpha=0.7, color='skyblue', edgecolor='black')\n",
    "axes[0].axvline(mean_salary, color='red', linestyle='--', linewidth=2, label=f'均值={mean_salary:.2f}')\n",
    "axes[0].axvline(median_salary, color='green', linestyle='--', linewidth=2, label=f'中位数={median_salary:.2f}')\n",
    "axes[0].set_xlabel('月薪(千元)', fontsize=12)\n",
    "axes[0].set_ylabel('频数', fontsize=12)\n",
    "axes[0].set_title('员工薪资分布直方图', fontsize=14, fontweight='bold')\n",
    "axes[0].legend(fontsize=10)\n",
    "axes[0].grid(axis='y', alpha=0.3)\n",
    "\n",
    "# 右图:箱线图\n",
    "bp = axes[1].boxplot(salary_data['月薪'], vert=True, patch_artist=True,\n",
    "                      boxprops=dict(facecolor='lightblue', color='blue'),\n",
    "                      medianprops=dict(color='red', linewidth=2),\n",
    "                      whiskerprops=dict(color='blue'),\n",
    "                      capprops=dict(color='blue'))\n",
    "axes[1].set_ylabel('月薪(千元)', fontsize=12)\n",
    "axes[1].set_title('员工薪资箱线图', fontsize=14, fontweight='bold')\n",
    "axes[1].grid(axis='y', alpha=0.3)\n",
    "axes[1].set_xticklabels(['员工薪资'])\n",
    "\n",
    "plt.tight_layout()\n",
    "plt.show()\n",
    "\n",
    "print(\"📊 图表说明:\")\n",
    "print(\"- 左图:红色虚线=均值,绿色虚线=中位数,均值被高薪拉高\")\n",
    "print(\"- 右图:箱线图显示薪资分布,上方的点为异常高薪\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 二、离散程度度量(Measures of Dispersion)\n",
    "\n",
    "离散程度描述数据的\"分散情况\",回答\"数据波动有多大\"的问题。\n",
    "\n",
    "### 2.1 极差(Range)\n",
    "- **定义**: 最大值 - 最小值\n",
    "- **特点**: 简单直观,但只考虑极端值\n",
    "- **公式**: $Range = x_{max} - x_{min}$\n",
    "\n",
    "### 2.2 方差(Variance)\n",
    "- **定义**: 各数据与均值差的平方的平均值\n",
    "- **特点**: 衡量数据偏离均值的程度\n",
    "- **公式**: $s^2 = \\frac{\\sum_{i=1}^{n}(x_i - \\bar{x})^2}{n-1}$\n",
    "\n",
    "### 2.3 标准差(Standard Deviation)\n",
    "- **定义**: 方差的平方根\n",
    "- **特点**: 与原始数据单位一致,更直观\n",
    "- **公式**: $s = \\sqrt{s^2}$\n",
    "\n",
    "### 2.4 四分位数(Quartiles)\n",
    "- **Q1(第一四分位数)**: 25%的数据小于此值\n",
    "- **Q2(第二四分位数)**: 即中位数,50%的数据小于此值\n",
    "- **Q3(第三四分位数)**: 75%的数据小于此值\n",
    "- **IQR(四分位距)**: Q3 - Q1,衡量数据的中间50%的分散程度\n",
    "\n",
    "### 2.5 变异系数(Coefficient of Variation)\n",
    "- **定义**: 标准差与均值的比值\n",
    "- **特点**: 消除量纲影响,可比较不同单位数据的离散程度\n",
    "- **公式**: $CV = \\frac{s}{\\bar{x}} \\times 100\\%$"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 计算离散程度指标\n",
    "salary_range = salary_data['月薪'].max() - salary_data['月薪'].min()\n",
    "salary_var = salary_data['月薪'].var()  # 样本方差(除以n-1)\n",
    "salary_std = salary_data['月薪'].std()  # 样本标准差\n",
    "salary_q1 = salary_data['月薪'].quantile(0.25)\n",
    "salary_q2 = salary_data['月薪'].quantile(0.50)  # 即中位数\n",
    "salary_q3 = salary_data['月薪'].quantile(0.75)\n",
    "salary_iqr = salary_q3 - salary_q1\n",
    "salary_cv = (salary_std / mean_salary) * 100  # 变异系数\n",
    "\n",
    "print(\"=\"*60)\n",
    "print(\"离散程度分析结果\")\n",
    "print(\"=\"*60)\n",
    "print(f\"极差(Range):         {salary_range:.2f}千元\")\n",
    "print(f\"方差(Variance):      {salary_var:.2f}\")\n",
    "print(f\"标准差(Std Dev):     {salary_std:.2f}千元\")\n",
    "print(f\"变异系数(CV):        {salary_cv:.2f}%\")\n",
    "print(\"-\"*60)\n",
    "print(\"四分位数分析:\")\n",
    "print(f\"  Q1(25%分位数):     {salary_q1:.2f}千元\")\n",
    "print(f\"  Q2(50%分位数/中位数): {salary_q2:.2f}千元\")\n",
    "print(f\"  Q3(75%分位数):     {salary_q3:.2f}千元\")\n",
    "print(f\"  IQR(四分位距):     {salary_iqr:.2f}千元\")\n",
    "print(\"=\"*60)\n",
    "\n",
    "print(\"\\n💡 分析结论:\")\n",
    "print(f\"标准差为{salary_std:.2f}千元,说明员工薪资波动较大。\")\n",
    "print(f\"50%的员工薪资在{salary_q1:.2f}-{salary_q3:.2f}千元之间(IQR范围)。\")\n",
    "print(f\"变异系数为{salary_cv:.2f}%,薪资分散程度较高。\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 创建两组对比数据:A部门vs B部门\n",
    "np.random.seed(42)\n",
    "\n",
    "# A部门:薪资集中,波动小\n",
    "dept_a = np.random.normal(loc=15, scale=2, size=50)\n",
    "\n",
    "# B部门:薪资分散,波动大\n",
    "dept_b = np.random.normal(loc=15, scale=6, size=50)\n",
    "\n",
    "comparison_data = pd.DataFrame({\n",
    "    'A部门薪资': dept_a,\n",
    "    'B部门薪资': dept_b\n",
    "})\n",
    "\n",
    "# 计算两部门统计指标\n",
    "print(\"\\n两部门薪资对比:\")\n",
    "print(\"=\"*70)\n",
    "print(f\"{'指标':<15} {'A部门':<20} {'B部门':<20}\")\n",
    "print(\"=\"*70)\n",
    "print(f\"{'均值':<15} {comparison_data['A部门薪资'].mean():<20.2f} {comparison_data['B部门薪资'].mean():<20.2f}\")\n",
    "print(f\"{'中位数':<15} {comparison_data['A部门薪资'].median():<20.2f} {comparison_data['B部门薪资'].median():<20.2f}\")\n",
    "print(f\"{'标准差':<15} {comparison_data['A部门薪资'].std():<20.2f} {comparison_data['B部门薪资'].std():<20.2f}\")\n",
    "print(f\"{'变异系数(%)':<15} {(comparison_data['A部门薪资'].std()/comparison_data['A部门薪资'].mean()*100):<20.2f} {(comparison_data['B部门薪资'].std()/comparison_data['B部门薪资'].mean()*100):<20.2f}\")\n",
    "print(\"=\"*70)\n",
    "print(\"\\n💡 结论: 两部门平均薪资相近,但B部门薪资波动更大,说明薪资差异更明显\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 可视化对比两部门薪资分布\n",
    "fig, axes = plt.subplots(1, 2, figsize=(14, 5))\n",
    "\n",
    "# 左图:重叠直方图\n",
    "axes[0].hist(comparison_data['A部门薪资'], bins=20, alpha=0.6, color='blue', label='A部门(波动小)', edgecolor='black')\n",
    "axes[0].hist(comparison_data['B部门薪资'], bins=20, alpha=0.6, color='red', label='B部门(波动大)', edgecolor='black')\n",
    "axes[0].set_xlabel('月薪(千元)', fontsize=12)\n",
    "axes[0].set_ylabel('频数', fontsize=12)\n",
    "axes[0].set_title('两部门薪资分布对比', fontsize=14, fontweight='bold')\n",
    "axes[0].legend(fontsize=10)\n",
    "axes[0].grid(axis='y', alpha=0.3)\n",
    "\n",
    "# 右图:并排箱线图\n",
    "bp = axes[1].boxplot([comparison_data['A部门薪资'], comparison_data['B部门薪资']], \n",
    "                      labels=['A部门', 'B部门'],\n",
    "                      patch_artist=True,\n",
    "                      boxprops=dict(facecolor='lightblue'),\n",
    "                      medianprops=dict(color='red', linewidth=2))\n",
    "axes[1].set_ylabel('月薪(千元)', fontsize=12)\n",
    "axes[1].set_title('两部门薪资箱线图对比', fontsize=14, fontweight='bold')\n",
    "axes[1].grid(axis='y', alpha=0.3)\n",
    "\n",
    "plt.tight_layout()\n",
    "plt.show()\n",
    "\n",
    "print(\"📊 图表说明:\")\n",
    "print(\"- 左图:B部门薪资分布更分散(红色),A部门更集中(蓝色)\")\n",
    "print(\"- 右图:B部门箱体更长,表示数据离散程度更大\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 三、分布形态度量(Measures of Distribution Shape)\n",
    "\n",
    "### 3.1 偏度(Skewness)\n",
    "衡量数据分布的**对称性**:\n",
    "- **偏度=0**: 完全对称(正态分布)\n",
    "- **偏度>0**: 右偏(正偏),长尾在右侧,众数<中位数<均值\n",
    "- **偏度<0**: 左偏(负偏),长尾在左侧,均值<中位数<众数\n",
    "- **判断标准**: \n",
    "  - |偏度| < 0.5: 基本对称\n",
    "  - 0.5 ≤ |偏度| < 1: 中等偏斜\n",
    "  - |偏度| ≥ 1: 高度偏斜\n",
    "\n",
    "### 3.2 峰度(Kurtosis)\n",
    "衡量数据分布的**尖峭程度**:\n",
    "- **峰度=0**: 正态分布\n",
    "- **峰度>0**: 尖峭分布,数据集中在均值附近,尾部较厚\n",
    "- **峰度<0**: 平坦分布,数据分散,尾部较薄\n",
    "- **判断标准**:\n",
    "  - |峰度| < 3: 接近正态\n",
    "  - 峰度 ≥ 3: 尖峭分布\n",
    "  - 峰度 ≤ -3: 平坦分布"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 创建三种不同分布的数据\n",
    "np.random.seed(42)\n",
    "\n",
    "# 1. 正态分布(对称)\n",
    "normal_dist = np.random.normal(loc=100, scale=15, size=1000)\n",
    "\n",
    "# 2. 右偏分布(如收入、房价)\n",
    "right_skew = np.random.gamma(shape=2, scale=20, size=1000) + 50\n",
    "\n",
    "# 3. 左偏分布(如考试成绩)\n",
    "left_skew = 100 - np.random.gamma(shape=2, scale=10, size=1000)\n",
    "\n",
    "dist_data = pd.DataFrame({\n",
    "    '正态分布': normal_dist,\n",
    "    '右偏分布': right_skew,\n",
    "    '左偏分布': left_skew\n",
    "})\n",
    "\n",
    "# 计算偏度和峰度\n",
    "print(\"=\"*80)\n",
    "print(\"三种分布的形态特征\")\n",
    "print(\"=\"*80)\n",
    "print(f\"{'分布类型':<12} {'偏度(Skew)':<15} {'峰度(Kurt)':<15} {'形态描述':<20}\")\n",
    "print(\"=\"*80)\n",
    "\n",
    "for col in dist_data.columns:\n",
    "    skew = dist_data[col].skew()\n",
    "    kurt = dist_data[col].kurt()\n",
    "    \n",
    "    # 判断偏度\n",
    "    if abs(skew) < 0.5:\n",
    "        skew_desc = \"基本对称\"\n",
    "    elif skew >= 0.5:\n",
    "        skew_desc = \"右偏(正偏)\"\n",
    "    else:\n",
    "        skew_desc = \"左偏(负偏)\"\n",
    "    \n",
    "    # 判断峰度\n",
    "    if abs(kurt) < 0.5:\n",
    "        kurt_desc = \"正态峰度\"\n",
    "    elif kurt > 0.5:\n",
    "        kurt_desc = \"尖峭分布\"\n",
    "    else:\n",
    "        kurt_desc = \"平坦分布\"\n",
    "    \n",
    "    desc = f\"{skew_desc},{kurt_desc}\"\n",
    "    print(f\"{col:<12} {skew:<15.3f} {kurt:<15.3f} {desc:<20}\")\n",
    "\n",
    "print(\"=\"*80)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 可视化三种分布\n",
    "fig, axes = plt.subplots(2, 3, figsize=(16, 10))\n",
    "\n",
    "colors = ['blue', 'red', 'green']\n",
    "for idx, col in enumerate(dist_data.columns):\n",
    "    # 上排:直方图+密度曲线\n",
    "    axes[0, idx].hist(dist_data[col], bins=40, alpha=0.7, color=colors[idx], edgecolor='black', density=True)\n",
    "    axes[0, idx].axvline(dist_data[col].mean(), color='red', linestyle='--', linewidth=2, label=f'均值={dist_data[col].mean():.1f}')\n",
    "    axes[0, idx].axvline(dist_data[col].median(), color='green', linestyle='--', linewidth=2, label=f'中位数={dist_data[col].median():.1f}')\n",
    "    axes[0, idx].set_title(f\"{col}\\n偏度={dist_data[col].skew():.2f}\", fontsize=12, fontweight='bold')\n",
    "    axes[0, idx].set_xlabel('数值', fontsize=10)\n",
    "    axes[0, idx].set_ylabel('密度', fontsize=10)\n",
    "    axes[0, idx].legend(fontsize=8)\n",
    "    axes[0, idx].grid(axis='y', alpha=0.3)\n",
    "    \n",
    "    # 下排:Q-Q图(检验正态性)\n",
    "    stats.probplot(dist_data[col], dist=\"norm\", plot=axes[1, idx])\n",
    "    axes[1, idx].set_title(f\"{col} - Q-Q图\", fontsize=12, fontweight='bold')\n",
    "    axes[1, idx].grid(alpha=0.3)\n",
    "\n",
    "plt.tight_layout()\n",
    "plt.show()\n",
    "\n",
    "print(\"📊 图表说明:\")\n",
    "print(\"- 上排:直方图显示数据分布形态,红线=均值,绿线=中位数\")\n",
    "print(\"  * 正态分布:均值≈中位数,对称\")\n",
    "print(\"  * 右偏分布:均值>中位数,长尾在右\")\n",
    "print(\"  * 左偏分布:均值<中位数,长尾在左\")\n",
    "print(\"- 下排:Q-Q图,点越接近直线说明越接近正态分布\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 四、Pandas快速统计函数\n",
    "\n",
    "### 4.1 describe() - 一站式统计摘要"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 创建一个综合数据集:电商销售数据\n",
    "np.random.seed(42)\n",
    "\n",
    "n_records = 500\n",
    "sales_data = pd.DataFrame({\n",
    "    '订单编号': [f'ORD{str(i).zfill(5)}' for i in range(1, n_records+1)],\n",
    "    '商品类目': np.random.choice(['电子产品', '服装', '食品', '家居', '图书'], n_records, p=[0.3, 0.25, 0.2, 0.15, 0.1]),\n",
    "    '销售额': np.random.gamma(shape=2, scale=500, size=n_records) + 100,\n",
    "    '销售数量': np.random.poisson(lam=5, size=n_records) + 1,\n",
    "    '折扣率': np.random.uniform(0, 0.5, n_records),\n",
    "    '客户评分': np.random.choice([1, 2, 3, 4, 5], n_records, p=[0.05, 0.1, 0.15, 0.35, 0.35])\n",
    "})\n",
    "\n",
    "# 计算利润(简化模型)\n",
    "sales_data['利润'] = sales_data['销售额'] * (0.3 - sales_data['折扣率'] * 0.5)\n",
    "\n",
    "print(\"电商销售数据预览:\")\n",
    "print(sales_data.head(10))\n",
    "print(f\"\\n数据集大小: {sales_data.shape[0]}行 × {sales_data.shape[1]}列\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 使用describe()进行快速统计\n",
    "print(\"=\"*100)\n",
    "print(\"销售数据描述性统计摘要 - describe()\")\n",
    "print(\"=\"*100)\n",
    "print(sales_data.describe())\n",
    "print(\"=\"*100)\n",
    "\n",
    "print(\"\\n💡 describe()输出说明:\")\n",
    "print(\"  count: 非空值数量\")\n",
    "print(\"  mean:  平均值\")\n",
    "print(\"  std:   标准差\")\n",
    "print(\"  min:   最小值\")\n",
    "print(\"  25%:   第一四分位数(Q1)\")\n",
    "print(\"  50%:   第二四分位数(中位数,Q2)\")\n",
    "print(\"  75%:   第三四分位数(Q3)\")\n",
    "print(\"  max:   最大值\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# describe()的高级用法\n",
    "\n",
    "# 1. 包含所有列(包括非数值列)\n",
    "print(\"\\n1️⃣ 包含所有列的统计(include='all'):\")\n",
    "print(\"=\"*100)\n",
    "print(sales_data.describe(include='all'))\n",
    "\n",
    "# 2. 只看分类列\n",
    "print(\"\\n2️⃣ 只看分类列的统计(include='object'):\")\n",
    "print(\"=\"*100)\n",
    "print(sales_data.describe(include='object'))\n",
    "print(\"\\n说明: count=非空数,unique=唯一值数,top=最高频值,freq=最高频次\")\n",
    "\n",
    "# 3. 自定义百分位数\n",
    "print(\"\\n3️⃣ 自定义百分位数(5%, 25%, 50%, 75%, 95%):\")\n",
    "print(\"=\"*100)\n",
    "print(sales_data[['销售额', '利润']].describe(percentiles=[0.05, 0.25, 0.5, 0.75, 0.95]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.2 其他常用统计函数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 单列统计函数演示\n",
    "print(\"=\"*60)\n",
    "print(\"销售额的各种统计指标\")\n",
    "print(\"=\"*60)\n",
    "print(f\"总和(sum):           {sales_data['销售额'].sum():.2f}元\")\n",
    "print(f\"平均值(mean):        {sales_data['销售额'].mean():.2f}元\")\n",
    "print(f\"中位数(median):      {sales_data['销售额'].median():.2f}元\")\n",
    "print(f\"众数(mode):          {sales_data['销售额'].mode()[0]:.2f}元\")\n",
    "print(f\"标准差(std):         {sales_data['销售额'].std():.2f}元\")\n",
    "print(f\"方差(var):           {sales_data['销售额'].var():.2f}\")\n",
    "print(f\"最小值(min):         {sales_data['销售额'].min():.2f}元\")\n",
    "print(f\"最大值(max):         {sales_data['销售额'].max():.2f}元\")\n",
    "print(f\"极差(max-min):       {sales_data['销售额'].max() - sales_data['销售额'].min():.2f}元\")\n",
    "print(f\"25%分位数(Q1):       {sales_data['销售额'].quantile(0.25):.2f}元\")\n",
    "print(f\"75%分位数(Q3):       {sales_data['销售额'].quantile(0.75):.2f}元\")\n",
    "print(f\"四分位距(IQR):       {sales_data['销售额'].quantile(0.75) - sales_data['销售额'].quantile(0.25):.2f}元\")\n",
    "print(f\"偏度(skew):          {sales_data['销售额'].skew():.3f}\")\n",
    "print(f\"峰度(kurt):          {sales_data['销售额'].kurt():.3f}\")\n",
    "print(\"=\"*60)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 多列同时计算\n",
    "print(\"\\n多列统计汇总:\")\n",
    "print(\"=\"*100)\n",
    "stats_summary = pd.DataFrame({\n",
    "    '销售额': [\n",
    "        sales_data['销售额'].mean(),\n",
    "        sales_data['销售额'].median(),\n",
    "        sales_data['销售额'].std(),\n",
    "        sales_data['销售额'].min(),\n",
    "        sales_data['销售额'].max()\n",
    "    ],\n",
    "    '利润': [\n",
    "        sales_data['利润'].mean(),\n",
    "        sales_data['利润'].median(),\n",
    "        sales_data['利润'].std(),\n",
    "        sales_data['利润'].min(),\n",
    "        sales_data['利润'].max()\n",
    "    ],\n",
    "    '销售数量': [\n",
    "        sales_data['销售数量'].mean(),\n",
    "        sales_data['销售数量'].median(),\n",
    "        sales_data['销售数量'].std(),\n",
    "        sales_data['销售数量'].min(),\n",
    "        sales_data['销售数量'].max()\n",
    "    ]\n",
    "}, index=['均值', '中位数', '标准差', '最小值', '最大值'])\n",
    "\n",
    "print(stats_summary)\n",
    "print(\"=\"*100)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 五、分组统计分析\n",
    "\n",
    "实际业务中,我们经常需要**按类别进行统计对比**,例如:\n",
    "- 各商品类目的平均销售额\n",
    "- 不同地区的销售业绩\n",
    "- 各年龄段客户的消费特征"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 按商品类目分组统计\n",
    "print(\"=\"*100)\n",
    "print(\"按商品类目分组统计\")\n",
    "print(\"=\"*100)\n",
    "\n",
    "category_stats = sales_data.groupby('商品类目').agg({\n",
    "    '销售额': ['count', 'sum', 'mean', 'median', 'std', 'min', 'max'],\n",
    "    '利润': ['sum', 'mean'],\n",
    "    '销售数量': ['sum', 'mean'],\n",
    "    '客户评分': 'mean'\n",
    "})\n",
    "\n",
    "# 美化列名\n",
    "category_stats.columns = ['_'.join(col).strip() for col in category_stats.columns.values]\n",
    "category_stats = category_stats.round(2)\n",
    "\n",
    "print(category_stats)\n",
    "print(\"=\"*100)\n",
    "\n",
    "# 找出最畅销和最赚钱的类目\n",
    "best_selling = category_stats['销售额_sum'].idxmax()\n",
    "most_profitable = category_stats['利润_sum'].idxmax()\n",
    "highest_rated = category_stats['客户评分_mean'].idxmax()\n",
    "\n",
    "print(f\"\\n💡 业务洞察:\")\n",
    "print(f\"  📈 销售额最高的类目: {best_selling} (总销售额={category_stats.loc[best_selling, '销售额_sum']:.2f}元)\")\n",
    "print(f\"  💰 利润最高的类目:   {most_profitable} (总利润={category_stats.loc[most_profitable, '利润_sum']:.2f}元)\")\n",
    "print(f\"  ⭐ 评分最高的类目:   {highest_rated} (平均评分={category_stats.loc[highest_rated, '客户评分_mean']:.2f}分)\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 可视化分组统计结果\n",
    "fig, axes = plt.subplots(2, 2, figsize=(15, 12))\n",
    "\n",
    "# 1. 各类目销售额对比\n",
    "category_sales = sales_data.groupby('商品类目')['销售额'].sum().sort_values(ascending=False)\n",
    "axes[0, 0].bar(category_sales.index, category_sales.values, color='steelblue', edgecolor='black')\n",
    "axes[0, 0].set_title('各类目总销售额对比', fontsize=13, fontweight='bold')\n",
    "axes[0, 0].set_ylabel('销售额(元)', fontsize=11)\n",
    "axes[0, 0].tick_params(axis='x', rotation=45)\n",
    "axes[0, 0].grid(axis='y', alpha=0.3)\n",
    "# 添加数值标签\n",
    "for i, v in enumerate(category_sales.values):\n",
    "    axes[0, 0].text(i, v + 1000, f'{v:.0f}', ha='center', fontsize=9)\n",
    "\n",
    "# 2. 各类目平均客单价对比\n",
    "category_avg = sales_data.groupby('商品类目')['销售额'].mean().sort_values(ascending=False)\n",
    "axes[0, 1].barh(category_avg.index, category_avg.values, color='coral', edgecolor='black')\n",
    "axes[0, 1].set_title('各类目平均客单价对比', fontsize=13, fontweight='bold')\n",
    "axes[0, 1].set_xlabel('平均销售额(元)', fontsize=11)\n",
    "axes[0, 1].grid(axis='x', alpha=0.3)\n",
    "# 添加数值标签\n",
    "for i, v in enumerate(category_avg.values):\n",
    "    axes[0, 1].text(v + 20, i, f'{v:.0f}', va='center', fontsize=9)\n",
    "\n",
    "# 3. 各类目销售额箱线图(显示分布)\n",
    "sales_by_category = [sales_data[sales_data['商品类目']==cat]['销售额'].values for cat in category_sales.index]\n",
    "bp = axes[1, 0].boxplot(sales_by_category, labels=category_sales.index, patch_artist=True,\n",
    "                         boxprops=dict(facecolor='lightgreen'),\n",
    "                         medianprops=dict(color='red', linewidth=2))\n",
    "axes[1, 0].set_title('各类目销售额分布(箱线图)', fontsize=13, fontweight='bold')\n",
    "axes[1, 0].set_ylabel('销售额(元)', fontsize=11)\n",
    "axes[1, 0].tick_params(axis='x', rotation=45)\n",
    "axes[1, 0].grid(axis='y', alpha=0.3)\n",
    "\n",
    "# 4. 各类目客户评分分布\n",
    "category_rating = sales_data.groupby('商品类目')['客户评分'].mean().sort_values(ascending=False)\n",
    "colors_map = plt.cm.RdYlGn(category_rating.values / 5)  # 根据评分映射颜色\n",
    "bars = axes[1, 1].bar(category_rating.index, category_rating.values, color=colors_map, edgecolor='black')\n",
    "axes[1, 1].set_title('各类目平均客户评分', fontsize=13, fontweight='bold')\n",
    "axes[1, 1].set_ylabel('平均评分', fontsize=11)\n",
    "axes[1, 1].set_ylim([0, 5.5])\n",
    "axes[1, 1].tick_params(axis='x', rotation=45)\n",
    "axes[1, 1].grid(axis='y', alpha=0.3)\n",
    "axes[1, 1].axhline(y=4, color='red', linestyle='--', alpha=0.5, label='优秀线(4分)')\n",
    "axes[1, 1].legend(fontsize=9)\n",
    "# 添加数值标签\n",
    "for i, v in enumerate(category_rating.values):\n",
    "    axes[1, 1].text(i, v + 0.1, f'{v:.2f}', ha='center', fontsize=9)\n",
    "\n",
    "plt.tight_layout()\n",
    "plt.show()\n",
    "\n",
    "print(\"\\n📊 图表说明:\")\n",
    "print(\"  左上: 总销售额对比 - 看整体规模\")\n",
    "print(\"  右上: 平均客单价对比 - 看单笔价值\")\n",
    "print(\"  左下: 销售额分布箱线图 - 看价格波动\")\n",
    "print(\"  右下: 客户评分对比 - 看客户满意度\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 六、交叉表统计(Cross-tabulation)\n",
    "\n",
    "交叉表用于**分析两个或多个分类变量之间的关系**。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 添加地区和时间维度\n",
    "sales_data['地区'] = np.random.choice(['华东', '华南', '华北', '西南'], len(sales_data), p=[0.35, 0.25, 0.25, 0.15])\n",
    "sales_data['季度'] = np.random.choice(['Q1', 'Q2', 'Q3', 'Q4'], len(sales_data))\n",
    "\n",
    "print(\"添加地区和季度维度后的数据:\")\n",
    "print(sales_data.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 1. 简单交叉表:类目 × 地区的订单数量\n",
    "print(\"\\n1️⃣ 各地区各类目订单数量交叉表:\")\n",
    "print(\"=\"*80)\n",
    "crosstab1 = pd.crosstab(sales_data['商品类目'], sales_data['地区'], margins=True)\n",
    "print(crosstab1)\n",
    "print(\"=\"*80)\n",
    "print(\"说明: margins=True 添加合计行和列\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 2. 带百分比的交叉表\n",
    "print(\"\\n2️⃣ 各地区各类目订单占比(按行百分比):\")\n",
    "print(\"=\"*80)\n",
    "crosstab2 = pd.crosstab(sales_data['商品类目'], sales_data['地区'], normalize='index')  # normalize='index'按行计算百分比\n",
    "print((crosstab2 * 100).round(2))  # 转换为百分比\n",
    "print(\"=\"*80)\n",
    "print(\"说明: normalize='index'(按行), 'columns'(按列), 'all'(总体)\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 3. 带统计量的交叉表(透视表形式)\n",
    "print(\"\\n3️⃣ 各地区各类目平均销售额交叉表:\")\n",
    "print(\"=\"*80)\n",
    "pivot_table = pd.crosstab(\n",
    "    sales_data['商品类目'], \n",
    "    sales_data['地区'], \n",
    "    values=sales_data['销售额'], \n",
    "    aggfunc='mean',  # 聚合函数\n",
    "    margins=True\n",
    ").round(2)\n",
    "print(pivot_table)\n",
    "print(\"=\"*80)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 4. 多层交叉表:类目 × 地区 × 季度\n",
    "print(\"\\n4️⃣ 三维交叉表:各季度各地区各类目订单数:\")\n",
    "print(\"=\"*100)\n",
    "crosstab3 = pd.crosstab(\n",
    "    [sales_data['季度'], sales_data['商品类目']],  # 行索引(多层)\n",
    "    sales_data['地区'],  # 列索引\n",
    "    margins=True\n",
    ")\n",
    "print(crosstab3)\n",
    "print(\"=\"*100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 可视化交叉表:热力图\n",
    "fig, axes = plt.subplots(1, 2, figsize=(16, 6))\n",
    "\n",
    "# 左图:订单数量热力图\n",
    "crosstab_plot1 = pd.crosstab(sales_data['商品类目'], sales_data['地区'])\n",
    "sns.heatmap(crosstab_plot1, annot=True, fmt='d', cmap='Blues', ax=axes[0], cbar_kws={'label': '订单数'})\n",
    "axes[0].set_title('各地区各类目订单数量热力图', fontsize=13, fontweight='bold')\n",
    "axes[0].set_xlabel('地区', fontsize=11)\n",
    "axes[0].set_ylabel('商品类目', fontsize=11)\n",
    "\n",
    "# 右图:平均销售额热力图\n",
    "crosstab_plot2 = pd.crosstab(sales_data['商品类目'], sales_data['地区'], values=sales_data['销售额'], aggfunc='mean')\n",
    "sns.heatmap(crosstab_plot2, annot=True, fmt='.0f', cmap='YlOrRd', ax=axes[1], cbar_kws={'label': '平均销售额(元)'})\n",
    "axes[1].set_title('各地区各类目平均销售额热力图', fontsize=13, fontweight='bold')\n",
    "axes[1].set_xlabel('地区', fontsize=11)\n",
    "axes[1].set_ylabel('商品类目', fontsize=11)\n",
    "\n",
    "plt.tight_layout()\n",
    "plt.show()\n",
    "\n",
    "print(\"\\n📊 图表说明:\")\n",
    "print(\"  左图: 颜色越深表示订单数量越多\")\n",
    "print(\"  右图: 颜色越深表示平均销售额越高\")\n",
    "print(\"  可快速发现:哪些地区哪些类目表现最好/最差\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 七、相关性分析\n",
    "\n",
    "相关性分析用于衡量**两个数值变量之间的线性关系强度**。\n",
    "\n",
    "### 皮尔逊相关系数(Pearson Correlation)\n",
    "- **取值范围**: -1 到 +1\n",
    "- **r = 1**: 完全正相关\n",
    "- **r = 0**: 无线性相关\n",
    "- **r = -1**: 完全负相关\n",
    "- **判断标准**:\n",
    "  - |r| < 0.3: 弱相关\n",
    "  - 0.3 ≤ |r| < 0.7: 中等相关\n",
    "  - |r| ≥ 0.7: 强相关"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 计算数值列的相关系数矩阵\n",
    "numeric_cols = ['销售额', '销售数量', '折扣率', '客户评分', '利润']\n",
    "correlation_matrix = sales_data[numeric_cols].corr()\n",
    "\n",
    "print(\"=\"*80)\n",
    "print(\"各变量相关系数矩阵\")\n",
    "print(\"=\"*80)\n",
    "print(correlation_matrix.round(3))\n",
    "print(\"=\"*80)\n",
    "\n",
    "print(\"\\n💡 关键发现:\")\n",
    "# 找出强相关的变量对\n",
    "strong_corr = []\n",
    "for i in range(len(correlation_matrix.columns)):\n",
    "    for j in range(i+1, len(correlation_matrix.columns)):\n",
    "        corr_value = correlation_matrix.iloc[i, j]\n",
    "        if abs(corr_value) >= 0.5:  # 中等以上相关\n",
    "            var1 = correlation_matrix.columns[i]\n",
    "            var2 = correlation_matrix.columns[j]\n",
    "            strong_corr.append((var1, var2, corr_value))\n",
    "\n",
    "if strong_corr:\n",
    "    for var1, var2, corr in sorted(strong_corr, key=lambda x: abs(x[2]), reverse=True):\n",
    "        direction = \"正相关\" if corr > 0 else \"负相关\"\n",
    "        strength = \"强\" if abs(corr) >= 0.7 else \"中等\"\n",
    "        print(f\"  {var1} 与 {var2}: {corr:.3f} ({strength}{direction})\")\nelse:\n",
    "    print(\"  未发现中等以上强度的相关关系\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 可视化相关系数矩阵\n",
    "fig, axes = plt.subplots(1, 2, figsize=(16, 6))\n",
    "\n",
    "# 左图:相关系数热力图\n",
    "sns.heatmap(correlation_matrix, annot=True, fmt='.3f', cmap='coolwarm', center=0,\n",
    "            square=True, linewidths=1, cbar_kws={'label': '相关系数'}, ax=axes[0],\n",
    "            vmin=-1, vmax=1)\n",
    "axes[0].set_title('变量相关系数热力图', fontsize=13, fontweight='bold')\n",
    "\n",
    "# 右图:散点图矩阵(选取几个关键变量)\n",
    "from pandas.plotting import scatter_matrix\n",
    "selected_cols = ['销售额', '销售数量', '折扣率', '利润']\n",
    "scatter_matrix(sales_data[selected_cols], figsize=(8, 8), alpha=0.5, diagonal='hist', ax=axes[1])\n",
    "axes[1].set_title('关键变量散点图矩阵', fontsize=13, fontweight='bold')\n",
    "\n",
    "plt.tight_layout()\n",
    "plt.show()\n",
    "\n",
    "print(\"\\n📊 图表说明:\")\n",
    "print(\"  左图: 热力图,红色=正相关,蓝色=负相关,颜色越深相关性越强\")\n",
    "print(\"  右图: 散点图矩阵,对角线=分布直方图,非对角线=两变量散点图\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 八、综合案例:员工薪资分析报告\n",
    "\n",
    "现在综合运用所有描述性统计方法,完成一份完整的数据分析报告。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 创建更真实的员工数据集\n",
    "np.random.seed(42)\n",
    "\n",
    "n_employees = 300\n",
    "employee_data = pd.DataFrame({\n",
    "    '员工ID': [f'E{str(i).zfill(4)}' for i in range(1, n_employees+1)],\n",
    "    '部门': np.random.choice(['技术', '销售', '市场', '运营', '财务'], n_employees, p=[0.35, 0.25, 0.15, 0.15, 0.1]),\n",
    "    '职级': np.random.choice(['初级', '中级', '高级', '专家'], n_employees, p=[0.4, 0.35, 0.2, 0.05]),\n",
    "    '工作年限': np.random.poisson(lam=3, size=n_employees) + 1,\n",
    "    '月薪': np.random.normal(loc=15, scale=5, size=n_employees),\n",
    "    '年终奖': np.random.gamma(shape=2, scale=5, size=n_employees),\n",
    "    '绩效评分': np.random.choice([60, 70, 80, 90, 100], n_employees, p=[0.05, 0.15, 0.4, 0.3, 0.1]),\n",
    "    '学历': np.random.choice(['本科', '硕士', '博士'], n_employees, p=[0.6, 0.35, 0.05])\n",
    "})\n",
    "\n",
    "# 根据职级调整薪资(更真实)\n",
    "level_multiplier = {'初级': 0.8, '中级': 1.0, '高级': 1.5, '专家': 2.2}\n",
    "employee_data['月薪'] = employee_data.apply(\n",
    "    lambda row: row['月薪'] * level_multiplier[row['职级']], axis=1\n",
    ")\n",
    "\n",
    "# 根据工作年限调整薪资\n",
    "employee_data['月薪'] = employee_data['月薪'] + employee_data['工作年限'] * 0.5\n",
    "\n",
    "# 根据绩效调整年终奖\n",
    "employee_data['年终奖'] = employee_data['年终奖'] * (employee_data['绩效评分'] / 80)\n",
    "\n",
    "# 计算年收入\n",
    "employee_data['年收入'] = employee_data['月薪'] * 12 + employee_data['年终奖']\n",
    "\n",
    "# 确保非负值\n",
    "employee_data['月薪'] = employee_data['月薪'].clip(lower=5)\n",
    "employee_data['年终奖'] = employee_data['年终奖'].clip(lower=0)\n",
    "\n",
    "print(\"员工数据集预览:\")\n",
    "print(employee_data.head(15))\n",
    "print(f\"\\n数据集规模: {employee_data.shape[0]}行 × {employee_data.shape[1]}列\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 第一部分:整体统计概览\n",
    "print(\"\\n\" + \"=\"*100)\n",
    "print(\"📊 员工薪资分析报告\")\n",
    "print(\"=\"*100)\n",
    "\n",
    "print(\"\\n【一、整体薪资统计】\")\n",
    "print(\"-\"*100)\n",
    "salary_desc = employee_data[['月薪', '年终奖', '年收入', '工作年限', '绩效评分']].describe()\n",
    "print(salary_desc.round(2))\n",
    "print(\"-\"*100)\n",
    "\n",
    "print(f\"\\n关键指标:\")\n",
    "print(f\"  平均月薪: {employee_data['月薪'].mean():.2f}千元 (中位数: {employee_data['月薪'].median():.2f}千元)\")\n",
    "print(f\"  平均年收入: {employee_data['年收入'].mean():.2f}千元\")\n",
    "print(f\"  薪资标准差: {employee_data['月薪'].std():.2f}千元 (变异系数: {(employee_data['月薪'].std()/employee_data['月薪'].mean()*100):.2f}%)\")\n",
    "print(f\"  薪资偏度: {employee_data['月薪'].skew():.3f} ({'右偏' if employee_data['月薪'].skew() > 0 else '左偏'})\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 第二部分:分部门统计\n",
    "print(\"\\n【二、分部门薪资对比】\")\n",
    "print(\"-\"*100)\n",
    "dept_stats = employee_data.groupby('部门').agg({\n",
    "    '月薪': ['count', 'mean', 'median', 'std', 'min', 'max'],\n",
    "    '年终奖': 'mean',\n",
    "    '年收入': 'mean',\n",
    "    '绩效评分': 'mean',\n",
    "    '工作年限': 'mean'\n",
    "}).round(2)\n",
    "dept_stats.columns = ['_'.join(col).strip() for col in dept_stats.columns.values]\n",
    "print(dept_stats)\n",
    "print(\"-\"*100)\n",
    "\n",
    "highest_dept = dept_stats['月薪_mean'].idxmax()\n",
    "lowest_dept = dept_stats['月薪_mean'].idxmin()\n",
    "print(f\"\\n部门洞察:\")\n",
    "print(f\"  薪资最高部门: {highest_dept} (平均月薪: {dept_stats.loc[highest_dept, '月薪_mean']:.2f}千元)\")\n",
    "print(f\"  薪资最低部门: {lowest_dept} (平均月薪: {dept_stats.loc[lowest_dept, '月薪_mean']:.2f}千元)\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 第三部分:分职级统计\n",
    "print(\"\\n【三、分职级薪资对比】\")\n",
    "print(\"-\"*100)\n",
    "level_order = ['初级', '中级', '高级', '专家']\n",
    "level_stats = employee_data.groupby('职级').agg({\n",
    "    '月薪': ['count', 'mean', 'median', 'std'],\n",
    "    '年收入': 'mean',\n",
    "    '工作年限': 'mean',\n",
    "    '绩效评分': 'mean'\n",
    "}).round(2)\n",
    "level_stats.columns = ['_'.join(col).strip() for col in level_stats.columns.values]\n",
    "level_stats = level_stats.reindex(level_order)\n",
    "print(level_stats)\n",
    "print(\"-\"*100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 第四部分:交叉分析(部门×职级)\n",
    "print(\"\\n【四、部门×职级交叉分析】\")\n",
    "print(\"-\"*100)\n",
    "cross_analysis = pd.crosstab(\n",
    "    employee_data['部门'], \n",
    "    employee_data['职级'],\n",
    "    values=employee_data['月薪'],\n",
    "    aggfunc='mean'\n",
    ")[level_order].round(2)\n",
    "print(cross_analysis)\n",
    "print(\"-\"*100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 第五部分:相关性分析\n",
    "print(\"\\n【五、薪资影响因素相关性分析】\")\n",
    "print(\"-\"*100)\n",
    "corr_cols = ['月薪', '年终奖', '年收入', '工作年限', '绩效评分']\n",
    "correlation = employee_data[corr_cols].corr()['月薪'].sort_values(ascending=False)\n",
    "print(correlation.round(3))\n",
    "print(\"-\"*100)\n",
    "print(\"\\n相关性解读:\")\n",
    "for var, corr_val in correlation.items():\n",
    "    if var != '月薪':\n",
    "        if abs(corr_val) >= 0.7:\n",
    "            strength = \"强\"\n",
    "        elif abs(corr_val) >= 0.3:\n",
    "            strength = \"中等\"\n",
    "        else:\n",
    "            strength = \"弱\"\n",
    "        direction = \"正\" if corr_val > 0 else \"负\"\n",
    "        print(f\"  {var} 与月薪: {strength}{direction}相关 (r={corr_val:.3f})\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 综合可视化报告\n",
    "fig = plt.figure(figsize=(18, 12))\n",
    "gs = fig.add_gridspec(3, 3, hspace=0.3, wspace=0.3)\n",
    "\n",
    "# 1. 整体薪资分布\n",
    "ax1 = fig.add_subplot(gs[0, :])\n",
    "ax1.hist(employee_data['月薪'], bins=40, alpha=0.7, color='skyblue', edgecolor='black')\n",
    "ax1.axvline(employee_data['月薪'].mean(), color='red', linestyle='--', linewidth=2, label=f\"均值={employee_data['月薪'].mean():.1f}\")\n",
    "ax1.axvline(employee_data['月薪'].median(), color='green', linestyle='--', linewidth=2, label=f\"中位数={employee_data['月薪'].median():.1f}\")\n",
    "ax1.set_title('全公司月薪分布直方图', fontsize=14, fontweight='bold')\n",
    "ax1.set_xlabel('月薪(千元)', fontsize=11)\n",
    "ax1.set_ylabel('人数', fontsize=11)\n",
    "ax1.legend(fontsize=10)\n",
    "ax1.grid(axis='y', alpha=0.3)\n",
    "\n",
    "# 2. 各部门薪资箱线图\n",
    "ax2 = fig.add_subplot(gs[1, 0])\n",
    "dept_order = dept_stats.sort_values('月薪_mean', ascending=False).index\n",
    "employee_data_sorted = employee_data.set_index('部门').loc[dept_order].reset_index()\n",
    "bp1 = ax2.boxplot([employee_data[employee_data['部门']==d]['月薪'].values for d in dept_order],\n",
    "                   labels=dept_order, patch_artist=True,\n",
    "                   boxprops=dict(facecolor='lightcoral'))\n",
    "ax2.set_title('各部门月薪分布', fontsize=12, fontweight='bold')\n",
    "ax2.set_ylabel('月薪(千元)', fontsize=10)\n",
    "ax2.tick_params(axis='x', rotation=45)\n",
    "ax2.grid(axis='y', alpha=0.3)\n",
    "\n",
    "# 3. 各职级薪资箱线图\n",
    "ax3 = fig.add_subplot(gs[1, 1])\n",
    "bp2 = ax3.boxplot([employee_data[employee_data['职级']==l]['月薪'].values for l in level_order],\n",
    "                   labels=level_order, patch_artist=True,\n",
    "                   boxprops=dict(facecolor='lightgreen'))\n",
    "ax3.set_title('各职级月薪分布', fontsize=12, fontweight='bold')\n",
    "ax3.set_ylabel('月薪(千元)', fontsize=10)\n",
    "ax3.grid(axis='y', alpha=0.3)\n",
    "\n",
    "# 4. 薪资与工作年限散点图\n",
    "ax4 = fig.add_subplot(gs[1, 2])\n",
    "for dept in employee_data['部门'].unique():\n",
    "    dept_df = employee_data[employee_data['部门']==dept]\n",
    "    ax4.scatter(dept_df['工作年限'], dept_df['月薪'], alpha=0.6, label=dept, s=50)\n",
    "ax4.set_title('月薪 vs 工作年限', fontsize=12, fontweight='bold')\n",
    "ax4.set_xlabel('工作年限(年)', fontsize=10)\n",
    "ax4.set_ylabel('月薪(千元)', fontsize=10)\n",
    "ax4.legend(fontsize=8, loc='upper left')\n",
    "ax4.grid(alpha=0.3)\n",
    "\n",
    "# 5. 部门×职级热力图\n",
    "ax5 = fig.add_subplot(gs[2, 0])\n",
    "sns.heatmap(cross_analysis, annot=True, fmt='.1f', cmap='YlOrRd', ax=ax5, cbar_kws={'label': '平均月薪'})\n",
    "ax5.set_title('部门×职级平均月薪热力图', fontsize=12, fontweight='bold')\n",
    "ax5.set_xlabel('职级', fontsize=10)\n",
    "ax5.set_ylabel('部门', fontsize=10)\n",
    "\n",
    "# 6. 绩效与薪资关系\n",
    "ax6 = fig.add_subplot(gs[2, 1])\n",
    "performance_salary = employee_data.groupby('绩效评分')['月薪'].mean().sort_index()\n",
    "ax6.plot(performance_salary.index, performance_salary.values, marker='o', linewidth=2, markersize=8, color='purple')\n",
    "ax6.set_title('平均月薪 vs 绩效评分', fontsize=12, fontweight='bold')\n",
    "ax6.set_xlabel('绩效评分', fontsize=10)\n",
    "ax6.set_ylabel('平均月薪(千元)', fontsize=10)\n",
    "ax6.grid(alpha=0.3)\n",
    "\n",
    "# 7. 相关系数热力图\n",
    "ax7 = fig.add_subplot(gs[2, 2])\n",
    "corr_matrix = employee_data[corr_cols].corr()\n",
    "sns.heatmap(corr_matrix, annot=True, fmt='.2f', cmap='coolwarm', center=0, ax=ax7,\n",
    "            square=True, linewidths=1, cbar_kws={'label': '相关系数'})\n",
    "ax7.set_title('变量相关系数矩阵', fontsize=12, fontweight='bold')\n",
    "\n",
    "plt.suptitle('员工薪资分析可视化报告', fontsize=16, fontweight='bold', y=0.995)\n",
    "plt.show()\n",
    "\n",
    "print(\"\\n✅ 可视化报告生成完成!\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 第六部分:异常值检测\n",
    "print(\"\\n【六、薪资异常值检测】\")\n",
    "print(\"-\"*100)\n",
    "\n",
    "# 使用IQR方法检测异常值\n",
    "Q1 = employee_data['月薪'].quantile(0.25)\n",
    "Q3 = employee_data['月薪'].quantile(0.75)\n",
    "IQR = Q3 - Q1\n",
    "lower_bound = Q1 - 1.5 * IQR\n",
    "upper_bound = Q3 + 1.5 * IQR\n",
    "\n",
    "outliers = employee_data[(employee_data['月薪'] < lower_bound) | (employee_data['月薪'] > upper_bound)]\n",
    "\n",
    "print(f\"IQR检测参数:\")\n",
    "print(f\"  Q1 (25%分位数): {Q1:.2f}千元\")\n",
    "print(f\"  Q3 (75%分位数): {Q3:.2f}千元\")\n",
    "print(f\"  IQR: {IQR:.2f}千元\")\n",
    "print(f\"  异常值判定范围: [{lower_bound:.2f}, {upper_bound:.2f}]千元\")\n",
    "print(f\"\\n检测到 {len(outliers)} 个薪资异常值 (占比: {len(outliers)/len(employee_data)*100:.2f}%)\")\n",
    "\n",
    "if len(outliers) > 0:\n",
    "    print(\"\\n异常值详情:\")\n",
    "    print(outliers[['员工ID', '部门', '职级', '工作年限', '月薪', '绩效评分']].sort_values('月薪', ascending=False))\n",
    "print(\"-\"*100)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 九、课程总结\n",
    "\n",
    "### 核心知识点\n",
    "\n",
    "1. **集中趋势度量**\n",
    "   - 均值 `mean()`: 整体水平,受极端值影响\n",
    "   - 中位数 `median()`: 中间位置,稳健指标\n",
    "   - 众数 `mode()`: 最常见值,适用分类数据\n",
    "\n",
    "2. **离散程度度量**\n",
    "   - 极差: max - min\n",
    "   - 标准差 `std()`: 衡量波动性\n",
    "   - 方差 `var()`: 标准差的平方\n",
    "   - 四分位数 `quantile()`: 分位数分析\n",
    "   - 变异系数: std/mean,消除量纲影响\n",
    "\n",
    "3. **分布形态度量**\n",
    "   - 偏度 `skew()`: 对称性,判断左偏/右偏\n",
    "   - 峰度 `kurt()`: 尖峭程度\n",
    "\n",
    "4. **Pandas核心函数**\n",
    "   - `describe()`: 一站式统计摘要\n",
    "   - `groupby().agg()`: 分组统计\n",
    "   - `crosstab()`: 交叉表分析\n",
    "   - `corr()`: 相关性分析\n",
    "\n",
    "### 业务应用场景\n",
    "\n",
    "- **薪资分析**: 用中位数代表典型薪资,避免被高管薪资误导\n",
    "- **销售分析**: 用标准差衡量销售稳定性,波动大需关注\n",
    "- **客户分析**: 用偏度判断客户消费是否集中\n",
    "- **质量控制**: 用变异系数比较不同产品的质量稳定性\n",
    "- **交叉分析**: 多维度对比(如地区×产品×季度)\n",
    "\n",
    "### Excel vs Pandas\n",
    "\n",
    "| 优势 | Excel | Pandas |\n",
    "|------|-------|--------|\n",
    "| 数据量 | <10万行 | 百万级+ |\n",
    "| 自动化 | 手动操作 | 脚本化,可重复 |\n",
    "| 复杂统计 | 有限 | 丰富(偏度、峰度等) |\n",
    "| 可视化 | 图表功能强 | 结合matplotlib更灵活 |\n",
    "| 学习成本 | 低 | 中等 |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 十、课后作业\n",
    "\n",
    "### 作业1:学生成绩统计分析(基础)\n",
    "\n",
    "某班级50名学生的数学、语文、英语成绩如下,请完成:\n",
    "1. 计算每科的均值、中位数、标准差\n",
    "2. 找出每科成绩最高和最低的学生\n",
    "3. 计算每个学生的总分和平均分\n",
    "4. 用箱线图可视化三科成绩分布\n",
    "5. 分析三科成绩的相关性"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 作业1数据生成\n",
    "np.random.seed(42)\n",
    "n_students = 50\n",
    "\n",
    "homework1_data = pd.DataFrame({\n",
    "    '学号': [f'S{str(i).zfill(3)}' for i in range(1, n_students+1)],\n",
    "    '数学': np.random.normal(75, 12, n_students).clip(0, 100).round(1),\n",
    "    '语文': np.random.normal(78, 10, n_students).clip(0, 100).round(1),\n",
    "    '英语': np.random.normal(72, 15, n_students).clip(0, 100).round(1)\n",
    "})\n",
    "\n",
    "print(\"作业1数据:\")\n",
    "print(homework1_data.head(10))\n",
    "\n",
    "# TODO: 在此处完成作业1\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 作业2:超市商品销售分析(进阶)\n",
    "\n",
    "某超市一个月内各类商品的销售数据如下,请完成:\n",
    "1. 使用describe()查看整体统计\n",
    "2. 按商品类别分组,计算平均销售额和销售数量\n",
    "3. 制作类别×是否促销的交叉表,显示平均销售额\n",
    "4. 检测销售额的异常值(使用IQR方法)\n",
    "5. 分析销售额、销售数量、折扣之间的相关性\n",
    "6. 用热力图可视化相关系数矩阵"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 作业2数据生成\n",
    "np.random.seed(42)\n",
    "n_sales = 200\n",
    "\n",
    "homework2_data = pd.DataFrame({\n",
    "    '订单号': [f'O{str(i).zfill(5)}' for i in range(1, n_sales+1)],\n",
    "    '商品类别': np.random.choice(['食品', '日用品', '电器', '服装'], n_sales, p=[0.35, 0.3, 0.2, 0.15]),\n",
    "    '销售额': np.random.gamma(2, 200, n_sales) + 50,\n",
    "    '销售数量': np.random.poisson(3, n_sales) + 1,\n",
    "    '是否促销': np.random.choice(['是', '否'], n_sales, p=[0.3, 0.7]),\n",
    "    '折扣': np.random.uniform(0, 0.3, n_sales)\n",
    "})\n",
    "\n",
    "# 促销订单销售额提升20%\n",
    "homework2_data.loc[homework2_data['是否促销']=='是', '销售额'] *= 1.2\n",
    "homework2_data['销售额'] = homework2_data['销售额'].round(2)\n",
    "homework2_data['折扣'] = homework2_data['折扣'].round(3)\n",
    "\n",
    "print(\"作业2数据:\")\n",
    "print(homework2_data.head(10))\n",
    "\n",
    "# TODO: 在此处完成作业2\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 作业3:公司部门绩效分析(综合)\n",
    "\n",
    "某公司5个部门的季度绩效数据如下,请完成一份完整的统计分析报告:\n",
    "1. 整体描述性统计(均值、中位数、标准差、偏度、峰度)\n",
    "2. 按部门分组统计,找出表现最好和最差的部门\n",
    "3. 制作部门×季度的交叉表,分析各部门的季度趋势\n",
    "4. 分析营收、成本、利润之间的相关性\n",
    "5. 检测利润的异常值\n",
    "6. 综合可视化:包括分布图、箱线图、热力图、趋势图\n",
    "7. 撰写分析结论和业务建议"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 作业3数据生成\n",
    "np.random.seed(42)\n",
    "departments = ['销售部', '技术部', '运营部', '市场部', '客服部']\n",
    "quarters = ['Q1', 'Q2', 'Q3', 'Q4']\n",
    "\n",
    "homework3_data = pd.DataFrame([\n",
    "    {'部门': dept, '季度': q, \n",
    "     '营收': np.random.normal(500, 100, 1)[0],\n",
    "     '成本': np.random.normal(300, 50, 1)[0]}\n",
    "    for dept in departments for q in quarters\n",
    "])\n",
    "\n",
    "homework3_data['利润'] = homework3_data['营收'] - homework3_data['成本']\n",
    "homework3_data['利润率'] = (homework3_data['利润'] / homework3_data['营收'] * 100).round(2)\n",
    "homework3_data = homework3_data.round(2)\n",
    "\n",
    "print(\"作业3数据:\")\n",
    "print(homework3_data.head(12))\n",
    "\n",
    "# TODO: 在此处完成作业3\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## 课程结束\n",
    "\n",
    "**下一讲预告**: 第4阶段_第4讲_探索性数据分析(EDA)\n",
    "\n",
    "将学习:\n",
    "- 数据分布分析\n",
    "- 多变量关系探索\n",
    "- 数据可视化高级技巧\n",
    "- 完整的EDA流程\n",
    "\n",
    "---\n",
    "\n",
    "📧 如有疑问,请联系助教\n",
    "\n",
    "✅ 完成作业后,请提交Jupyter Notebook文件"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
